<!---
	Copyright 2010 Edward Trudeau, Jeremy Battle
    This software is freely available under the MIT License: http://www.opensource.org/licenses/mit-license.php
    http://ayc.googlecode.com
    
--->

<cfcomponent displayName="base" output="false">
	<cfset variables.instance = StructNew() />
	
	<cffunction name="init" access="public" returnType="loadFieldCat" output="false">
		<cfargument name="dsn" type="String" required="true" />				
		<cfargument name="tables" type="Array" required="true" />
		<cfargument name="objectName" type="String" required="true" />
		<cfargument name="dbType" type="String" required="false" default="MSSQL" />
		<cfargument name="whereStatement" type="String" required="false" />
		
		
		
		<cfset variables.objectName = arguments.objectName />
		<cfset variables.dsn = arguments.dsn />
		<cfset variables.tables = arguments.tables />

		
		<cfif structKeyExists(arguments,"whereStatement")>
			<cfset createBase(arguments.tables,arguments.dbType,arguments.whereStatement) />
		<cfelse>
			<cfset createBase(arguments.tables,arguments.dbType) />
		</cfif>
		
		<cfreturn this />
	</cffunction>

	<cffunction name="createBase" hint="I build the skeleton struct which is a struct that stores all fields, datatypes etc for the tables you pass me" returnType="void">
		<cfargument name="tables" type="Array" required="true" />
		<cfargument name="dbType" type="string" required="false" default="MSSQL" />
		<cfargument name="whereStatement" type="String" required="false" />
				
		<cfset var i = 0 />
		<cfset var thisTable = 0 />
		<cfset var qGetTableMeta = "" />
		<cfset var qGetCurrentValues = "" />
		<cfset var qTablePKs = "" />
		<cfset var lstPrimaryKeys = "" />
		<cfset var qTableFKs = "" />
	    <cfset var lstFKs = "" />
		<cfset var lstFKs_PKs = "" />
	    <cfset var lstFKs_PKTables = "" />
		<cfset variables.baseStruct = structNew() />
		<!--- Store the array of tables in case we need to loop over them later --->
		<cfset variables.baseStruct.tables = arguments.tables />		
		

		<!--- loop over the tables, grabbing all the fields and data about the fields --->		
		<cfloop from="1" to="#arrayLen(variables.baseStruct.tables)#" index="i">
			<cfif compareNoCase(arguments.dbType,"Oracle") EQ 0>
				<cfquery datasource="#variables.dsn#" name="qGetTableMeta">
				SELECT
             	    col.COLUMN_NAME       as COLUMN_NAME,
                  /* Oracle has no equivalent to autoincrement or  identity */
                  '0'                     AS "IDENTITY",                    
                  CASE
                        WHEN col.NULLABLE = 'Y' AND col.DATA_DEFAULT IS NULL THEN 1 /* a column is defined as nullable only if it doesn't have a default */
                        ELSE 0
                  END                  as NULLABLE,
                 col.DATA_TYPE         as TYPE_NAME,
                  case
                    /* 26 is the length of now() in ColdFusion (i.e. {ts '2006-06-26 13:10:14'})*/
                    when col.data_type = 'DATE'   then 26
                    else col.data_length
                  end                 as length,
                  col.DATA_DEFAULT      as "DEFAULT"
            FROM  all_tab_columns   col,
                  ( select  colCon.column_name,
                 			  colcon.table_name
                  from    all_cons_columns  colCon,
                         all_constraints   tabCon
                  where tabCon.table_name = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="128" value="#ucase(variables.baseStruct.tables[i])#" />
                       AND colCon.CONSTRAINT_NAME = tabCon.CONSTRAINT_NAME
                       AND colCon.TABLE_NAME      = tabCon.TABLE_NAME
                       AND 'P'                    = tabCon.CONSTRAINT_TYPE
                 ) primaryConstraints
            where col.table_name = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="128" value="#ucase(variables.baseStruct.tables[i])#" />
            		and col.COLUMN_NAME        = primaryConstraints.COLUMN_NAME (+)
                  AND col.TABLE_NAME       = primaryConstraints.TABLE_NAME (+)
        order by col.column_id
				</cfquery>		

				<cfquery name="qTablePKs" datasource="#variables.dsn#">
		      	SELECT    
					colCon.column_name,
		           	colcon.CONSTRAINT_NAME  AS PK_NAME
		        FROM
				    user_cons_columns  colCon,
		            user_constraints   tabCon
		        WHERE
					upper(tabCon.table_name) = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="128" value="#ucase(variables.baseStruct.tables[i])#" />
		        	AND colCon.CONSTRAINT_NAME = tabCon.CONSTRAINT_NAME
		        	AND colCon.TABLE_NAME      = tabCon.TABLE_NAME
		        	AND 'P'                    = tabCon.CONSTRAINT_TYPE
		        ORDER BY
					colcon.POSITION
				</cfquery>
				
				<cfset lstPrimaryKeys = valueList(qTablePKs.column_name) />
				
				<cfif structKeyExists(arguments,"whereStatement")>
					<cfquery datasource="#variables.dsn#" name="qGetCurrentValues">
						SELECT
							*
						FROM
							#ucase(variables.baseStruct.tables[i])#
						WHERE 
							ROWNUM = 1
						AND
							#preserveSingleQuotes(arguments.whereStatement)#
					</cfquery>
				</cfif>
			<cfelseif compareNoCase(arguments.dbType,"MSSQL") EQ 0>
				<cfquery name="qGetTableMeta" datasource="#variables.dsn#">
					SELECT	c.COLUMN_NAME,
						c.DATA_TYPE as TYPE_NAME,
						c.COLUMN_DEFAULT as [DEFAULT],
						CASE
							WHEN ISNUMERIC(c.CHARACTER_MAXIMUM_LENGTH) = 1 THEN c.CHARACTER_MAXIMUM_LENGTH
							ELSE 0
							END as LENGTH,
						CASE
							WHEN c.IS_NULLABLE = 'No' AND c.Column_Default IS NULL THEN 0 /* a column is defined as nullable only if it doesn't have a default */
							ELSE 1
						END as NULLABLE,
						CASE
							WHEN columnProperty(object_id(c.TABLE_NAME), c.COLUMN_NAME, 'IsIdentity') > 0 THEN '1'
							ELSE '0'
						END AS [IDENTITY]
					FROM INFORMATION_SCHEMA.COLUMNS as c
					WHERE UPPER(c.TABLE_NAME) = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="128" value="#ucase(variables.baseStruct.tables[i])#" />
				</cfquery>
				
				<cfquery name="qTablePKs" datasource="#variables.dsn#">
					SELECT ccu.COLUMN_NAME,ccu.CONSTRAINT_NAME AS PK_NAME
					FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
						INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
						ON ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
					AND 	UPPER(ccu.TABLE_NAME) = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="128" value="#ucase(variables.baseStruct.tables[i])#" />
					AND	tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
				</cfquery>	
				
				<cfset lstPrimaryKeys = valueList(qTablePKs.column_name) />
				
		        <cfstoredproc procedure="sp_fkeys" datasource="#variables.dsn#">
		            <cfprocparam cfsqltype="cf_sql_varchar" value="null" type="in" null="yes">
		            <cfprocparam cfsqltype="cf_sql_varchar" value="dbo" type="in">
		            <cfprocparam cfsqltype="cf_sql_varchar" value="default" type="in" null="yes">
		            <cfprocparam cfsqltype="cf_sql_varchar" value="#variables.baseStruct.tables[i]#" type="in">
		            <cfprocparam cfsqltype="cf_sql_varchar" value="dbo" type="in">
		            <cfprocparam cfsqltype="cf_sql_varchar" value="default" type="in" null="yes">
		            <cfprocresult name="qTableFKs">            
		        </cfstoredproc>
		        	
				<cfset lstFKs = valueList(qTableFKs.fkColumn_name) />
				<cfset lstFKs_PKs = valueList(qTableFKs.pkColumn_name) />
	            <cfset lstFKs_PKTables = valueList(qTableFKs.pkTable_name) />   
				
				<cfdump var="#qTableFKs#" />
				
				<cfif structKeyExists(arguments,"whereStatement")>
					<cfquery datasource="#variables.dsn#" name="qGetCurrentValues">
						SELECT TOP 1
							*
						FROM
							#ucase(variables.baseStruct.tables[i])#
						WHERE 
							#preserveSingleQuotes(arguments.whereStatement)#
					</cfquery>
				</cfif>
			</cfif>		 	
			<!--- Add this table to the skeleton struct --->
			<cfset variables.baseStruct[variables.baseStruct.tables[i]] = structNew() />
			
			<cfif isQuery(qGetTableMeta)>		

				<cfloop query="qGetTableMeta">
					<!--- Add each of the fields for this table to the skeleton struct--->
					<cfset variables.baseStruct[variables.baseStruct.tables[i]][column_name] = structNew() />
					<cfif structKeyExists(arguments,"whereStatement")>
						<cfset variables.baseStruct[variables.baseStruct.tables[i]][column_name].value = evaluate("qGetCurrentValues.#column_name#") />
					<cfelse>
						<cfset variables.baseStruct[variables.baseStruct.tables[i]][column_name].value = ""/>
					</cfif>
					<cfset variables.baseStruct[variables.baseStruct.tables[i]][column_name].tableName = variables.baseStruct.tables[i] />
					<cfset variables.baseStruct[variables.baseStruct.tables[i]][column_name].columnName = column_name />
					<cfset variables.baseStruct[variables.baseStruct.tables[i]][column_name].maxLength = length />
					<cfset variables.baseStruct[variables.baseStruct.tables[i]][column_name].isNullable = nullable />
					<cfset variables.baseStruct[variables.baseStruct.tables[i]][column_name].isIdentity = identity />
					<cfset variables.baseStruct[variables.baseStruct.tables[i]][column_name].default = default />
					<cfset variables.baseStruct[variables.baseStruct.tables[i]][column_name].isPrimaryKey = compareNoCase(listFindNoCase(lstPrimaryKeys,column_name),0) />
					<cfif arguments.dbType EQ "MSSQL">					
					<cfset variables.baseStruct[variables.baseStruct.tables[i]][column_name].isForeignKey = compareNoCase(listFindNoCase(lstFKs,column_name),0) />
						<cfif listFindNoCase(lstFKs,column_name)>
							<cfset variables.baseStruct[variables.baseStruct.tables[i]][column_name].pkColumnName = listGetAt(lstFKs_PKs, listFindNoCase(lstFKs,column_name)) />
							<cfset variables.baseStruct[variables.baseStruct.tables[i]][column_name].pkTableName = listGetAt(lstFKs_PKTables, listFindNoCase(lstFKs,column_name)) />
						<cfelse>
							<cfset variables.baseStruct[variables.baseStruct.tables[i]][column_name].pkColumnName = "" />
							<cfset variables.baseStruct[variables.baseStruct.tables[i]][column_name].pkTableName = "" />
						</cfif>
					<cfelse>
						<cfset variables.baseStruct[variables.baseStruct.tables[i]][column_name].isPrimaryKey = 0 />
						<cfset variables.baseStruct[variables.baseStruct.tables[i]][column_name].pkColumnName = "" />
						<cfset variables.baseStruct[variables.baseStruct.tables[i]][column_name].pkTableName = "" />
					</cfif>
					<cfif compareNoCase(arguments.dbType,"Oracle") EQ 0>
						<cfset variables.baseStruct[variables.baseStruct.tables[i]][column_name].cfsqlType = translateOracleCfSqlType(type_name) />
						<cfset variables.baseStruct[variables.baseStruct.tables[i]][column_name].dataType = translateOracleDataType(type_name) />
					<cfelseif compareNoCase(arguments.dbType,"MSSQL") EQ 0>
						<cfset variables.baseStruct[variables.baseStruct.tables[i]][column_name].cfsqlType = translateMSSQLCfSqlType(type_name) />
						<cfset variables.baseStruct[variables.baseStruct.tables[i]][column_name].dataType = translateMSSQLDataType(type_name) />
					</cfif>
				</cfloop>
				
				<cfset qGetTableMeta = "" />
				<cfset qGetCurrentValues = "" />
				<cfset qTablePKs = "" />
				<cfset lstPrimaryKeys = "" />
				<cfset thisTable = 0 />
				<cfset qTableFKs = "" />
	    		<cfset lstFKs = "" />
				<cfset lstFKs_PKs = "" />
	    		<cfset lstFKs_PKTables = "" />		
				
			</cfif>

		</cfloop>	

	</cffunction>
	
	<cffunction name="getBase" access="public" returntype="Struct" output="false">
		<cfreturn variables.baseStruct />
	</cffunction>
	
	<cffunction name="get" hint="I am a generic getter of properties for this bean">
		<cfargument name="tableName" required="true" hint="the tables whose field's property you want to set" />
		<cfargument name="fieldName" required="false" hint="the field whose property you want me to return" />
		<cfargument name="propertyName" required="false" hint="the property you want me to return" />
		
		<cfset var i = "" />
		<cfset var returnValue = "" />
		
		<cfif not structKeyExists(variables,"baseStruct")>
			<cfthrow message="Base was initialized but not created." />		
		</cfif>
		
		<cfscript>	
			if(structKeyExists(variables.baseStruct,"#arguments.tableName#"))
			{
				//table exists, continue to find field
				if(structKeyExists(arguments,"fieldName") and structKeyExists(variables.baseStruct["#arguments.tableName#"],"#arguments.fieldName#"))
				{	
					if(structKeyExists(arguments,"propertyName") and structKeyExists(variables.baseStruct["#arguments.tableName#"]["#arguments.fieldName#"],"#arguments.propertyName#"))
					{
						returnValue = variables.baseStruct["#arguments.tableName#"]["#arguments.fieldName#"]["#arguments.propertyName#"];
					}
					else if(structKeyExists(arguments,"propertyName") and NOT structKeyExists(variables.baseStruct["#arguments.tableName#"]["#arguments.fieldName#"],"#arguments.propertyName#"))
					{
						returnValue = "Property does not exist.";
					}
					else
					{
						returnValue = variables.baseStruct["#arguments.tableName#"]["#arguments.fieldName#"];
					}
				}
				else if(structKeyExists(arguments,"fieldName") and NOT structKeyExists(variables.baseStruct["#arguments.tableName#"],"#arguments.fieldName#"))
				{
					returnValue = "Field Not found";
				}
				else
				{
					returnValue =  variables.baseStruct["#arguments.tableName#"];
				}
			}
			else
			{
				returnValue = "Table Not found";
			}
		</cfscript>
		
		<cfreturn returnValue />
	</cffunction>
	<cffunction name="populateFieldCatalog" hint="I drop info about the fields in the fieldCatalog" returnType="struct">
		<cfargument name="dsn" required="yes" default="ayc" hint="datasource where the field catalog table lives" />
		
		<cfset var i = 0 />
		<cfset var table = "" />
		<cfset var qObjectExists = "" />
		<cfset var qRemoveObject = "" />
		<cfset var qInsertField = "" />
		<cfset var returnvalue = structNew() />

		<cftransaction>
		<!--- We need to know if this object exists in the database --->
			
			<cfquery datasource="#arguments.dsn#" name="qObjectExists">
			SELECT *
			FROM
				aycFieldCatalog
			WHERE
				ObjectName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.objectName#" maxlength="255" />
			</cfquery>
		
			<cfif qObjectExists.recordCount>
				<cfquery datasource="#arguments.dsn#" name="qRemoveObject">
				DELETE
				FROM
					aycFieldCatalog
				WHERE
					ObjectName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.objectName#" maxlength="255" />
				</cfquery>					
			</cfif>
			
			<cfset qObjectExists = "" />
			<cfset qRemoveObject = "" />
			
			<cfloop from="1" to="#arrayLen(variables.baseStruct.tables)#" index="i">			
				<cfloop collection="#variables.baseStruct[variables.baseStruct.tables[i]]#" item="table">
			
					<cfquery datasource="#arguments.dsn#" name="qInsertField">
					INSERT
					INTO aycFieldCatalog
						(
							ObjectName,
							FieldName,
							LabelText,
							FieldType,
							DefaultsTo,
							MaxLength
						)
					VALUES
						(
							<cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.objectName#" maxlength="255" />,
							<cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.baseStruct[variables.baseStruct.tables[i]][table].columnName#" maxlength="255" />,
							<cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.baseStruct[variables.baseStruct.tables[i]][table].columnName#" maxlength="255" />,
							<cfqueryparam cfsqltype="cf_sql_varchar" value="text" maxlength="255" />,
							<cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.baseStruct[variables.baseStruct.tables[i]][table].default#" maxlength="255" />,
							<cfif variables.baseStruct[variables.baseStruct.tables[i]][table].cfsqltype neq "cf_sql_varchar">
							<cfqueryparam cfsqltype="cf_sql_varchar" value="255" maxlength="255" />					
							<cfelse>
							<cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.baseStruct[variables.baseStruct.tables[i]][table].maxLength#" maxlength="255" />					
							</cfif>							
							)			
					</cfquery>
					<cfset qInsertField = "" />
				</cfloop>
			</cfloop>
		
		</cftransaction>
	
		<cfset returnValue.catch = "None, it worked!">
		<cfset returnValue.succes = true>
		<cfreturn returnValue>
	</cffunction>
	<cffunction name="set" hint="I am a generic setter of properties for this bean">
		<cfargument name="tableName" required="true"  hint="the tables whose field's property you want to set" />
		<cfargument name="fieldName" required="true" hint="the field whose property you want me to set" />		
		<cfargument name="propertyValue" required="true" hint="the value you want me to set the property to" />
		<cfargument name="propertyName" required="false" default="value" hint="the property you want me to set" />
		
		<cfset var errors = "" />
		<cfset var i = "" />
		
		<cfif not structKeyExists(variables,"baseStruct")>
			<cfthrow message="Skeleton was initialized but not created." />		
		</cfif>
		
		<cfscript>	
			if(structKeyExists(variables.baseStruct,"#arguments.tableName#"))
			{
				//table exists, continue to find field
				if(structKeyExists(variables.baseStruct["#arguments.tableName#"],"#arguments.fieldName#"))
				{				
					if(structKeyExists(variables.baseStruct["#arguments.tableName#"]["#arguments.fieldName#"],"#arguments.propertyName#"))
					{
						if(get("#arguments.tableName#","#arguments.fieldName#","maxLength") < len(trim(arguments.propertyValue)))
						{
							errors = errors & '<li>#arguments.fieldName# cannot be longer than #get("#arguments.fieldName#","maxLength")#</li>';
						}
						if(get("#arguments.tableName#","#arguments.fieldName#","isNullable") == 0 && len(trim(arguments.propertyValue)) == 0)
						{
							errors = errors & '<li>#arguments.fieldName# cannot be null</li>';
						}
						if(get("#arguments.tableName#","#arguments.fieldName#","isIdentity") == 1)
						{
							errors = errors & '<li>#arguments.fieldName# is an identity column and cannot be set</li>';
						}
						
						if(!len(trim(errors)))
						{
						variables.baseStruct["#arguments.tableName#"]["#arguments.fieldName#"]["#arguments.propertyName#"] = arguments.propertyValue;
						}
					}
					 else 
					{
						errors = errors & "<li>set of Property #arguments.propertyName# failed on Field #arguments.fieldName#. This Property #arguments.propertyName# does not appear to exist. [skeleton.cfc])</li>"; 
					}
				}
				else
				{
					errors = errors & '<li>#arguments.fieldName# does not appear to be related to #arguments.tableName#</li>';
				}			
			}
			else 
			{
				throw("set of Property #arguments.propertyName# failed on Field #arguments.fieldName#. This Field #arguments.fieldName# does not appear to exist. [skeleton.cfc])"); 
			}
			return errors;	
		</cfscript>
	</cffunction>

	<!--- Thank you Brian Rinaldi for these two functions --->
	<cffunction name="translateOracleCfSqlType" hint="I translate the Oracle data type names into ColdFusion cf_sql_xyz names" output="false" returntype="string">
		<cfargument name="typeName" hint="I am the type name to translate" required="yes" type="string" />
		<cfswitch expression="#lcase(arguments.typeName)#">
        <!--- misc --->
			<cfcase value="rowid">
				<cfreturn "cf_sql_varchar" />
			</cfcase>
			<!--- time --->
			<cfcase value="date">
				<cfreturn "cf_sql_timestamp" />
			</cfcase>
			<cfcase value="timestamp(6)">
				<cfreturn "cf_sql_date" />
			</cfcase>
         <!--- strings --->
			<cfcase value="char">
				<cfreturn "cf_sql_char" />
			</cfcase>
			<cfcase value="nchar">
				<cfreturn "cf_sql_char" />
			</cfcase>
			<cfcase value="varchar">
				<cfreturn "cf_sql_varchar" />
			</cfcase>
			<cfcase value="varchar2">
				<cfreturn "cf_sql_varchar" />
			</cfcase>
			<cfcase value="nvarchar2">
				<cfreturn "cf_sql_varchar" />
			</cfcase>
			<!--- long types --->
			<!---   @@Note: bfile  not supported --->
			<cfcase value="blob">
				<cfreturn "cf_sql_blob" />
			</cfcase>
			<cfcase value="clob">
				<cfreturn "cf_sql_clob" />
			</cfcase>
			<cfcase value="nclob">
				<cfreturn "cf_sql_clob" />
			</cfcase>
			<cfcase value="long">
				<cfreturn "cf_sql_longvarchar" />
			</cfcase>
			   <!--- @@Note: may need "tobinary(ToBase64(x))" when updating --->
			<cfcase value="long raw">
				<cfreturn "cf_sql_longvarbinary" />
			</cfcase>
			<cfcase value="raw">
			   <!--- @@Note: may need "tobinary(ToBase64(x))" when updating --->
				<cfreturn "cf_sql_varbinary" />
			</cfcase>
			<!--- numerics --->
			<cfcase value="float">
				<cfreturn "cf_sql_float" />
			</cfcase>
			<cfcase value="decimal">
				<cfreturn "cf_sql_decimal" />
			</cfcase>
			<cfcase value="integer">
				<cfreturn "cf_sql_numeric" />
			</cfcase>
			<cfcase value="number">
				<cfreturn "cf_sql_numeric" />
			</cfcase>
			<cfcase value="real">
				<cfreturn "cf_sql_numeric" />
			</cfcase>
		</cfswitch>
		<cfthrow message="Unsupported (or incorrectly supported) database datatype: #arguments.typeName#." />
	</cffunction>
	
	<cffunction name="translateOracleDataType" hint="I translate the Oracle data type names into ColdFusion data type names" output="false" returntype="string">
		<cfargument name="typeName" hint="I am the type name to translate" required="yes" type="string" />
		<cfswitch expression="#arguments.typeName#">
        <!--- misc --->
			<cfcase value="rowid">
				<cfreturn "string" />
			</cfcase>
			<!--- time --->
			<cfcase value="date">
				<cfreturn "date" />
			</cfcase>
			<cfcase value="timestamp(6)">
				<cfreturn "date" />
			</cfcase>
            <!--- strings --->
			<cfcase value="char">
				<cfreturn "string" />
			</cfcase>
			<cfcase value="nchar">
				<cfreturn "string" />
			</cfcase>
			<cfcase value="varchar">
				<cfreturn "string" />
			</cfcase>
			<cfcase value="varchar2">
				<cfreturn "string" />
			</cfcase>
			<cfcase value="nvarchar2">
				<cfreturn "string" />
			</cfcase>
			<!--- long --->
			<!---   @@Note: bfile  not supported --->
			<cfcase value="blob">
				<cfreturn "binary" />
			</cfcase>
			<cfcase value="clob">
				<cfreturn "string" />
			</cfcase>
			<cfcase value="nclob">
				<cfreturn "string" />
			</cfcase>
			<cfcase value="long">
				<cfreturn "string" />
			</cfcase>
		   <cfcase value="long raw">
				<cfreturn "binary" />
			</cfcase>
			<cfcase value="raw">
				<cfreturn "binary" />
			</cfcase>
			<!--- numerics --->
			<cfcase value="float">
				<cfreturn "numeric" />
			</cfcase>
			<cfcase value="decimal">
				<cfreturn "numeric" />
			</cfcase>
			<cfcase value="integer">
				<cfreturn "numeric" />
			</cfcase>
			<cfcase value="number">
				<cfreturn "numeric" />
			</cfcase>
			<cfcase value="real">
				<cfreturn "numeric" />
			</cfcase>
		</cfswitch>

		<cfthrow message="Unsupported (or incorrectly supported) database datatype: #arguments.typeName#." />

	</cffunction>
	
	<cffunction name="translateMSSQLCfSqlType" hint="I translate the MSSQL data type names into ColdFusion cf_sql_xyz names" output="false" returntype="string">
		<cfargument name="typeName" hint="I am the type name to translate" required="yes" type="string" />
		
		<cfswitch expression="#arguments.typeName#">
			<cfcase value="bigint">
				<cfreturn "cf_sql_bigint" />
			</cfcase>
			<cfcase value="binary">
				<cfreturn "cf_sql_binary" />
			</cfcase>
			<cfcase value="bit">
				<cfreturn "cf_sql_bit" />
			</cfcase>
			<cfcase value="char">
				<cfreturn "cf_sql_char" />
			</cfcase>
			<cfcase value="datetime">
				<cfreturn "cf_sql_timestamp" />
			</cfcase>
			<cfcase value="decimal">
				<cfreturn "cf_sql_decimal" />
			</cfcase>
			<cfcase value="float">
				<cfreturn "cf_sql_float" />
			</cfcase>
			<cfcase value="image">
				<cfreturn "cf_sql_longvarbinary" />
			</cfcase>
			<cfcase value="int">
				<cfreturn "cf_sql_integer" />
			</cfcase>
			<cfcase value="money">
				<cfreturn "cf_sql_money" />
			</cfcase>
			<cfcase value="nchar">
				<cfreturn "cf_sql_char" />
			</cfcase>
			<cfcase value="ntext">
				<cfreturn "cf_sql_longvarchar" />
			</cfcase>
			<cfcase value="numeric">
				<cfreturn "cf_sql_varchar" />
			</cfcase>
			<cfcase value="nvarchar">
				<cfreturn "cf_sql_varchar" />
			</cfcase>
			<cfcase value="real">
				<cfreturn "cf_sql_real" />
			</cfcase>
			<cfcase value="smalldatetime">
				<cfreturn "cf_sql_timestamp" />
			</cfcase>
			<cfcase value="smallint">
				<cfreturn "cf_sql_smallint" />
			</cfcase>
			<cfcase value="smallmoney">
				<cfreturn "cf_sql_decimal" />
			</cfcase>
			<cfcase value="text">
				<cfreturn "cf_sql_longvarchar" />
			</cfcase>
			<cfcase value="timestamp">
				<cfreturn "cf_sql_timestamp" />
			</cfcase>
			<cfcase value="tinyint">
				<cfreturn "cf_sql_tinyint" />
			</cfcase>
			<cfcase value="uniqueidentifier">
				<cfreturn "cf_sql_idstamp" />
			</cfcase>
			<cfcase value="varbinary">
				<cfreturn "cf_sql_varbinary" />
			</cfcase>
			<cfcase value="varchar">
				<cfreturn "cf_sql_varchar" />
			</cfcase>
			<cfcase value="xml">
				<cfreturn "cf_sql_longvarchar" />
			</cfcase>
			<cfcase value="sql_variant">
				<cfreturn "cf_sql_varchar" />
			</cfcase>
		</cfswitch>
	</cffunction>
	
	<cffunction name="translateMSSQLDataType" hint="I translate the MSSQL data type names into ColdFusion data type names" output="false" returntype="string">
		<cfargument name="typeName" hint="I am the type name to translate" required="yes" type="string" />
		
		<cfswitch expression="#arguments.typeName#">
			<cfcase value="bigint">
				<cfreturn "numeric" />
			</cfcase>
			<cfcase value="binary">
				<cfreturn "binary" />
			</cfcase>
			<cfcase value="bit">
				<cfreturn "boolean" />
			</cfcase>
			<cfcase value="char">
				<cfreturn "string" />
			</cfcase>
			<cfcase value="datetime">
				<cfreturn "date" />
			</cfcase>
			<cfcase value="decimal">
				<cfreturn "numeric" />
			</cfcase>
			<cfcase value="float">
				<cfreturn "numeric" />
			</cfcase>
			<cfcase value="image">
				<cfreturn "binary" />
			</cfcase>
			<cfcase value="int">
				<cfreturn "numeric" />
			</cfcase>
			<cfcase value="money">
				<cfreturn "numeric" />
			</cfcase>
			<cfcase value="nchar">
				<cfreturn "string" />
			</cfcase>
			<cfcase value="ntext">
				<cfreturn "string" />
			</cfcase>
			<cfcase value="numeric">
				<cfreturn "numeric" />
			</cfcase>
			<cfcase value="nvarchar">
				<cfreturn "string" />
			</cfcase>
			<cfcase value="real">
				<cfreturn "numeric" />
			</cfcase>
			<cfcase value="smalldatetime">
				<cfreturn "date" />
			</cfcase>
			<cfcase value="smallint">
				<cfreturn "numeric" />
			</cfcase>
			<cfcase value="smallmoney">
				<cfreturn "numeric" />
			</cfcase>
			<cfcase value="text">
				<cfreturn "string" />
			</cfcase>
			<cfcase value="timestamp">
				<cfreturn "numeric" />
			</cfcase>
			<cfcase value="tinyint">
				<cfreturn "numeric" />
			</cfcase>
			<cfcase value="uniqueidentifier">
				<cfreturn "string" />
			</cfcase>
			<cfcase value="varbinary">
				<cfreturn "binary" />
			</cfcase>
			<cfcase value="varchar">
				<cfreturn "string" />
			</cfcase>
			<cfcase value="xml">
				<cfreturn "string" />
			</cfcase>
			<cfcase value="sql_variant">
				<cfreturn "string" />
			</cfcase>
		</cfswitch>
	</cffunction>
</cfcomponent>


